package com.example.tms.controller;


import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.example.tms.bean.Peakwaybill;
import com.example.tms.bean.PeakwaybillView;
import com.example.tms.bean.ReturnInfo;
import com.example.tms.bean.WaybillVMgmt;
import com.example.tms.mapper.PeakwaybillMapper;
import com.example.tms.mapper.PeakwaybillVMapper;
import com.example.tms.tools.GlobalParam;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.example.tms.annotation.*;
/**
 * 高峰业务
 */
@RestController
public class PeakwaybillController {

    private final PeakwaybillMapper peakwaybillMapper;
    private final PeakwaybillVMapper peakwaybillVMapper;

    @Autowired
    public PeakwaybillController(PeakwaybillMapper peakwaybillMapper, PeakwaybillVMapper peakwaybillVMapper) {
        this.peakwaybillMapper = peakwaybillMapper;
        this.peakwaybillVMapper = peakwaybillVMapper;
    }

    private final Logger logger = LoggerFactory.getLogger(this.getClass());


    //查询高峰业务信息
    @RequestMapping(value = "/peak/{peakid}",method = RequestMethod.GET)
    public Peakwaybill getPeakwaybill(@PathVariable String peakid){
        return peakwaybillMapper.findOne(peakid);
    }

    //查询高峰业务信息列表兼搜索（分页）
    @PersistenceContext
    EntityManager entityManager;

    @RequestMapping(value = "/peak", method = RequestMethod.GET)
    public ReturnInfo getWaybillList1(@RequestParam(value = "pageNum", defaultValue = "1") String pageNum,
                                      @RequestParam(value = "pageSize", defaultValue = "0") String pageSize,
                                      @RequestParam(required = false, defaultValue = "") String quyu,
                                      @RequestParam(required = false, defaultValue = "") String kcsj,
                                      @RequestParam(required = false, defaultValue = "") String cfsj,
                                      @RequestParam(required = false, defaultValue = "") String tjsj,
                                      @RequestParam(required = false, defaultValue = "") String yewu1,
                                      @RequestParam(required = false, defaultValue = "") String yewu2,
                                      @RequestParam(required = false, defaultValue = "") String yewu3,
                                      @RequestParam(required = false, defaultValue = "") String yewu4,
                                      @RequestParam(required = false, defaultValue = "") String sfd,
                                      @RequestParam(required = false, defaultValue = "") String mdd,
                                      @RequestParam(required = false, defaultValue = "") String jsy){

        StringBuilder countSelectSql = new StringBuilder();
        countSelectSql.append("select count(1) from PeakwaybillView where 1=1 ");

        StringBuilder selectSql = new StringBuilder();
        selectSql.append("from PeakwaybillView where 1=1 ");

        ArrayList<String> yewus = new ArrayList<>();

        if (!yewu1.equals("")){
            yewus.add(yewu1);
        }
        if (!yewu2.equals("")){
            yewus.add(yewu2);
        }
        if (!yewu3.equals("")){
            yewus.add(yewu3);
        }
        if (!yewu4.equals("")){
            yewus.add(yewu4);
        }

        Map<String,Object> params = new HashMap<>();
        StringBuilder whereSql = new StringBuilder();
        if (!quyu.equals("")) {
            whereSql.append(" and ( quyu =:quyu or qyqt =:quyu) ");
            params.put("quyu",quyu);
        }
        if (!kcsj.equals("")) {
            whereSql.append(" and kcsj like CONCAT('%',:kcsj,'%') ");
            params.put("kcsj",kcsj);
        }
        if (!cfsj.equals("")) {
            whereSql.append(" and cfsj like CONCAT('%',:cfsj,'%') ");
            params.put("cfsj",cfsj);
        }
        if (!tjsj.equals("")) {
            whereSql.append(" and tjsj like CONCAT('%',:tjsj,'%') ");
            params.put("tjsj",tjsj);
        }
        if (!sfd.equals("")) {
            whereSql.append(" and sfd like CONCAT('%',:sfd,'%') ");
            params.put("sfd",sfd);
        }
        if (!mdd.equals("")) {
            whereSql.append(" and mdd like CONCAT('%',:mdd,'%') ");
            params.put("mdd",mdd);
        }
        if (!jsy.equals("")) {
            whereSql.append(" and jsy like CONCAT('%',:jsy,'%') ");
            params.put("jsy",jsy);
        }
        if (yewus.size()>0) {
            whereSql.append(" and ywlx in (:yewus) ");
            params.put("yewus",yewus);
        }

        String countSql = new StringBuilder().append(countSelectSql).append(whereSql).toString();
        TypedQuery<Long> countQuery = this.entityManager.createQuery(countSql,Long.class);
        this.setParameters(countQuery,params);
        Long count = (Long) countQuery.getSingleResult();

        String querySql = new StringBuilder().append(selectSql).append(whereSql).toString();
        Query query = this.entityManager.createQuery(querySql, PeakwaybillView.class);
        this.setParameters(query,params);

        if(pageNum != null && pageSize != null){ //分页
            query.setFirstResult((Integer.valueOf(pageNum)-1)*(Integer.valueOf(pageSize)));
            query.setMaxResults(Integer.valueOf(pageSize));
        }

        List<PeakwaybillView> DriverList = query.getResultList();
        return  ReturnInfo.info5(count.toString(),DriverList,0,"");
    }

    /**
     * 给hql参数设置值
     * @param query 查询
     * @param params 参数
     */
    private void setParameters(Query query,Map<String,Object> params){
        for(Map.Entry<String,Object> entry:params.entrySet()){
            query.setParameter(entry.getKey(),entry.getValue());
        }
    }

    @RequestMapping(value = "/peakwaybilllist",method = RequestMethod.GET)
    public ReturnInfo getPeakwaybillList(){
        ArrayList<String> quyuall = new ArrayList<>();
        List<String> quyus = peakwaybillVMapper.findQuyu();
        for(String quyu:quyus){
            if(quyu!=null&&!quyu.equals("")){
                quyuall.add(quyu);
            }
        }
        List<String> qyqts = peakwaybillVMapper.findQyqt();
        for(String qyqt:qyqts){
            if(qyqt!=null&&!qyqt.equals("")){
                quyuall.add(qyqt);
            }
        }
        return ReturnInfo.info2(quyuall);
    }

    //添加高峰业务
    @MonitorRequest
    @RequestMapping(value = "/peak",method = RequestMethod.POST)
    public List<Map<String,Object>> savePeakwaybill(@RequestBody Peakwaybill peakwaybill){
        List<Map<String,Object>>  list = new ArrayList<>();
        Map<String,Object>  map = new HashMap<>();
        if(peakwaybill.getYwlx()==null||peakwaybill.getYwlx().equals("")||!checkYwlx(peakwaybill.getYwlx())){
            map.put("ywlx", "0");
        }else if(peakwaybill.getYwlx().equals("其它")){
            if(peakwaybill.getYwqt()==null||peakwaybill.getYwqt().equals("")||!checkPeakwaybill(peakwaybill.getYwqt())){
                map.put("ywqt", "0");
            }
        }
        if(peakwaybill.getQuyu()==null||peakwaybill.getQuyu().equals("")||!checkPeakwaybill(peakwaybill.getQuyu())){
            map.put("quyu", "0");
        }else if(peakwaybill.getQuyu().equals("其它")){
            if(peakwaybill.getQyqt()==null||peakwaybill.getQyqt().equals("")||!checkPeakwaybill(peakwaybill.getQyqt())){
                map.put("qyqt", "0");
            }
        }
        if(peakwaybill.getSfd()==null||peakwaybill.getSfd().equals("")||!checkPeakwaybill(peakwaybill.getSfd())){
            map.put("sfd", "0");
        }
        if(peakwaybill.getMdd()==null||peakwaybill.getMdd().equals("")||!checkPeakwaybill(peakwaybill.getMdd())){
            map.put("mdd", "0");
        }
        if(peakwaybill.getKcsj()==null||peakwaybill.getKcsj().equals("")||!checkPeakwaybill(peakwaybill.getKcsj())){
            map.put("kcsj", "0");
        }
        if(peakwaybill.getCfsj()==null||peakwaybill.getCfsj().equals("")||!checkPeakwaybill(peakwaybill.getCfsj())){
            map.put("cfsj", "0");
        }
        if(peakwaybill.getCbh()==null||peakwaybill.getCbh().equals("")||!checkCbh(peakwaybill.getCbh())){
            map.put("cbh", "0");
        }
        if(peakwaybill.getChexing()==null||peakwaybill.getChexing().equals("")||!checkChexing(peakwaybill.getChexing())){
            map.put("chexing", "0");
        }else if(peakwaybill.getChexing().equals("30T")||peakwaybill.getChexing().equals("20T")){
            if(peakwaybill.getWgccph()==null||peakwaybill.getWgccph().equals("")||!checkPeakwaybill(peakwaybill.getWgccph())||!checkCph(peakwaybill.getWgccph())){
                map.put("wgccph", "0");
            }
        }
        if(peakwaybill.getClly()==null||peakwaybill.getClly().equals("")||!checkClly(peakwaybill.getClly())){
            map.put("clly", "0");
        }else if(peakwaybill.getClly().equals("外请车辆")){
                if(peakwaybill.getWqcclly()==null||peakwaybill.getWqcclly().equals("")||!checkPeakwaybill(peakwaybill.getWqcclly())||!checkCE(peakwaybill.getWqcclly())){
                    map.put("wqcclly", "0");
                }
                if(peakwaybill.getYkkh()==null||peakwaybill.getYkkh().equals("")||!checkYkkh(peakwaybill.getYkkh())){
                    map.put("ykkh", "0");
                }
            }
        if(peakwaybill.getCph()==null||peakwaybill.getCph().equals("")||!checkCph(peakwaybill.getCph())){
            map.put("cph", "0");
        }
        if(peakwaybill.getJsy()==null||peakwaybill.getJsy().equals("")||!checkPeakwaybill(peakwaybill.getJsy())||!checkCE(peakwaybill.getJsy())){
            map.put("jsy", "0");
        }
        if(peakwaybill.getSjhm()==null||peakwaybill.getSjhm().equals("")||!checkSjhm(peakwaybill.getSjhm())){
            map.put("sjhm", "0");
        }
        if(peakwaybill.getBysjhm()!=null&&!peakwaybill.getBysjhm().equals("")&&!checkSjhm(peakwaybill.getBysjhm())){
            map.put("bysjhm", "0");
        }
        if(peakwaybill.getYcbz()!=null&&!peakwaybill.getYcbz().equals("")&&!checkYcbz(peakwaybill.getYcbz())){
            map.put("ycbz", "0");
        }
        list.add(map);
        if(map.isEmpty()){
            peakwaybill.setTjsj(GlobalParam.getTime());
            peakwaybill.setLasttime(GlobalParam.getTime());
            peakwaybill.setSfcz(true);
            peakwaybillMapper.save(peakwaybill);
        }
        return list;
    }

    //修改高峰业务
    @MonitorRequest
    @RequestMapping(value = "/peak",method = RequestMethod.PUT)
    public List<Map<String,Object>> updatePeakwaybill(@RequestBody Peakwaybill peakwaybill){
        List<Map<String,Object>>  list = new ArrayList<>();
        Map<String,Object>  map = new HashMap<>();
        if(peakwaybill.getPeakid()==null||peakwaybillMapper.findOne(peakwaybill.getPeakid())==null){
            map.put("peakid", "0");//该车辆不存在
        }
        if(peakwaybill.getYwlx()==null||peakwaybill.getYwlx().equals("")||!checkYwlx(peakwaybill.getYwlx())){
            map.put("ywlx", "0");
        }else if(peakwaybill.getYwlx().equals("其它")){
            if(peakwaybill.getYwqt()==null||peakwaybill.getYwqt().equals("")||!checkPeakwaybill(peakwaybill.getYwqt())){
                map.put("ywqt", "0");
            }
        }
        if(peakwaybill.getQuyu()==null||peakwaybill.getQuyu().equals("")||!checkPeakwaybill(peakwaybill.getQuyu())){
            map.put("quyu", "0");
        }else if(peakwaybill.getQuyu().equals("其它")){
            if(peakwaybill.getQyqt()==null||peakwaybill.getQyqt().equals("")||!checkPeakwaybill(peakwaybill.getQyqt())){
                map.put("qyqt", "0");
            }
        }
        if(peakwaybill.getSfd()==null||peakwaybill.getSfd().equals("")||!checkPeakwaybill(peakwaybill.getSfd())){
            map.put("sfd", "0");
        }
        if(peakwaybill.getMdd()==null||peakwaybill.getMdd().equals("")||!checkPeakwaybill(peakwaybill.getMdd())){
            map.put("mdd", "0");
        }
        if(peakwaybill.getKcsj()==null||peakwaybill.getKcsj().equals("")||!checkPeakwaybill(peakwaybill.getKcsj())){
            map.put("kcsj", "0");
        }
        if(peakwaybill.getCfsj()==null||peakwaybill.getCfsj().equals("")||!checkPeakwaybill(peakwaybill.getCfsj())){
            map.put("cfsj", "0");
        }
        if(peakwaybill.getCbh()==null||peakwaybill.getCbh().equals("")||!checkCbh(peakwaybill.getCbh())){
            map.put("cbh", "0");
        }
        if(peakwaybill.getChexing()==null||peakwaybill.getChexing().equals("")||!checkChexing(peakwaybill.getChexing())){
            map.put("chexing", "0");
        }else if(peakwaybill.getChexing().equals("30T")||peakwaybill.getChexing().equals("20T")){
            if(peakwaybill.getWgccph()==null||peakwaybill.getWgccph().equals("")||!checkPeakwaybill(peakwaybill.getWgccph())||!checkCph(peakwaybill.getWgccph())){
                map.put("wgccph", "0");
            }
        }
        if(peakwaybill.getClly()==null||peakwaybill.getClly().equals("")||!checkClly(peakwaybill.getClly())){
            map.put("clly", "0");
        }else if(peakwaybill.getClly().equals("外请车辆")){
            if(peakwaybill.getWqcclly()==null||peakwaybill.getWqcclly().equals("")||!checkPeakwaybill(peakwaybill.getWqcclly())||!checkCE(peakwaybill.getWqcclly())){
                map.put("wqcclly", "0");
            }
            if(peakwaybill.getYkkh()==null||peakwaybill.getYkkh().equals("")||!checkYkkh(peakwaybill.getYkkh())){
                map.put("ykkh", "0");
            }
        }
        if(peakwaybill.getCph()==null||peakwaybill.getCph().equals("")||!checkCph(peakwaybill.getCph())){
            map.put("cph", "0");
        }
        if(peakwaybill.getJsy()==null||peakwaybill.getJsy().equals("")||!checkPeakwaybill(peakwaybill.getJsy())||!checkCE(peakwaybill.getJsy())){
            map.put("jsy", "0");
        }
        if(peakwaybill.getSjhm()==null||peakwaybill.getSjhm().equals("")||!checkSjhm(peakwaybill.getSjhm())){
            map.put("sjhm", "0");
        }
        if(peakwaybill.getBysjhm()!=null&&!peakwaybill.getBysjhm().equals("")&&!checkSjhm(peakwaybill.getBysjhm())){
            map.put("bysjhm", "0");
        }
        if(peakwaybill.getYcbz()!=null&&!peakwaybill.getYcbz().equals("")&&!checkYcbz(peakwaybill.getYcbz())){
            map.put("ycbz", "0");
        }
        list.add(map);
        if(map.isEmpty()){
            peakwaybill.setLasttime(GlobalParam.getTime());
            peakwaybill.setSfcz(true);
            peakwaybillMapper.saveAndFlush(peakwaybill);
        }
        return list;
    }

    //删除高峰业务信息（假删除 高峰业务id）
    @MonitorRequest
    @RequestMapping(value = "/peak/{peakid}",method = RequestMethod.DELETE)
    public ReturnInfo deleteCarinfo(@PathVariable String peakid){
        String b;
        if(peakid==null||peakwaybillMapper.findOne(peakid)==null) {
            b = "该信息不存在！";
        }else {
            peakwaybillMapper.DeleteByPeakid(peakid);
            b = "删除成功！";
        }
        return ReturnInfo.info1(b);
    }

    public List<PeakwaybillView> getPeakwaybillListexcel(@RequestParam(required = false, defaultValue = "") String yewu1,
                                                         @RequestParam(required = false, defaultValue = "") String yewu2,
                                                         @RequestParam(required = false, defaultValue = "") String yewu3,
                                                         @RequestParam(required = false, defaultValue = "") String yewu4,
                                                         @RequestParam(required = false, defaultValue = "") String quyu,
                                                         @RequestParam(required = false, defaultValue = "") String sfd,
                                                         @RequestParam(required = false, defaultValue = "") String mdd,
                                                         @RequestParam(required = false, defaultValue = "") String jsy,
                                                         @RequestParam(required = false, defaultValue = "") String tjsj,
                                                         @RequestParam(required = false, defaultValue = "") String cfsj,
                                                         @RequestParam(required = false, defaultValue = "") String kcsj) {
        List<PeakwaybillView> result = peakwaybillVMapper.findAll((Root<PeakwaybillView> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) -> {
            List<Predicate> list = new ArrayList<>();
            if (!quyu.equals("")) {
                list.add(criteriaBuilder.like(root.get("quyu").as(String.class), "%" + quyu + "%"));
            }
            if (!sfd.equals("")) {
                list.add(criteriaBuilder.like(root.get("sfd").as(String.class), "%" + sfd + "%"));
            }
            if (!mdd.equals("")) {
                list.add(criteriaBuilder.like(root.get("mdd").as(String.class), "%" + mdd + "%"));
            }
            if (!jsy.equals("")) {
                list.add(criteriaBuilder.like(root.get("jsy").as(String.class), "%" + jsy + "%"));
            }
            if (!cfsj.equals("")) {
                list.add(criteriaBuilder.like(root.get("cfsj").as(String.class), "%" + cfsj + "%"));
            }
            if (!tjsj.equals("")) {
                list.add(criteriaBuilder.like(root.get("tjsj").as(String.class), "%" + tjsj + "%"));
            }
            if (!kcsj.equals("")) {
                list.add(criteriaBuilder.like(root.get("kcsj").as(String.class), "%" + kcsj + "%"));
            }
            ArrayList<String> yewus = new ArrayList<>();
            if (!yewu1.equals("")){
                yewus.add(yewu1);
            }
            if (!yewu2.equals("")){
                yewus.add(yewu2);
            }
            if (!yewu3.equals("")){
                yewus.add(yewu3);
            }
            if (!yewu4.equals("")){
                yewus.add(yewu4);
            }
            if (yewus.size() > 0) {
                CriteriaBuilder.In<Object> in = criteriaBuilder.in(root.get("ywlx"));
                for (String ywlx : yewus) {
                    in.value(ywlx);
                }
                list.add(in);
            }
            Predicate[] p = new Predicate[list.size()];
            Predicate predicate = criteriaBuilder.and(list.toArray(p));
            criteriaQuery.where(predicate);
            return criteriaQuery.getRestriction();
        });
        return result;
    }

    @RequestMapping(value = "/peakexcel", method = RequestMethod.GET)
    public ReturnInfo getPeakExcel(HttpServletResponse response, HttpServletRequest request) throws UnsupportedEncodingException {
        String cfsj = "";
        String kcsj = "";
        String tjsj = "";
        String yewu1 = "";
        String yewu2 = "";
        String yewu3 = "";
        String yewu4 = "";
        String quyu = "";
        String jsy = "";
        String mdd = "";
        String sfd = "";


        if(request.getParameter("cfsj")!=null&&!request.getParameter("cfsj").equals("")){
            cfsj = request.getParameter("cfsj");
        }
        if(request.getParameter("kcsj")!=null&&!request.getParameter("kcsj").equals("")){
            kcsj = request.getParameter("kcsj");
        }
        if(request.getParameter("tjsj")!=null&&!request.getParameter("tjsj").equals("")){
            tjsj = request.getParameter("tjsj");
        }
        ArrayList<String> yewus = new ArrayList<>();
        if(request.getParameter("yewu1")!=null&&!request.getParameter("yewu1").equals("")){
            yewu1 = request.getParameter("yewu1");
            yewus.add(yewu1);
        }
        if(request.getParameter("yewu2")!=null&&!request.getParameter("yewu2").equals("")){
            yewu2 = request.getParameter("yewu2");
            yewus.add(yewu2);
        }
        if(request.getParameter("yewu3")!=null&&!request.getParameter("yewu3").equals("")){
            yewu3 = request.getParameter("yewu3");
            yewus.add(yewu3);
        }
        if(request.getParameter("yewu4")!=null&&!request.getParameter("yewu4").equals("")){
            yewu4 = request.getParameter("yewu4");
            yewus.add(yewu4);
        }
        if(request.getParameter("quyu")!=null&&!request.getParameter("quyu").equals("")){
            quyu = request.getParameter("quyu");
        }
        if(request.getParameter("jsy")!=null&&!request.getParameter("jsy").equals("")){
            jsy = request.getParameter("jsy");
        }
        if(request.getParameter("mdd")!=null&&!request.getParameter("mdd").equals("")){
            mdd = request.getParameter("mdd");
        }
        if(request.getParameter("sfd")!=null&&!request.getParameter("sfd").equals("")){
            sfd = request.getParameter("sfd");
        }
        String classPath = this.getClass().getClassLoader().getResource("").getPath();
        String path = classPath+"static/excel/";
        path=path.replaceAll("%20"," ");
        StringBuilder yewu = new StringBuilder();
        for(String yw : yewus){
            yewu.append(yw);
        }
        String filename = yewu+GlobalParam.getSSj()+".xls";
        String newpath = path+ filename;
        try {
            creatPeakwaybillExcel(getPeakwaybillListexcel(yewu1,yewu2,yewu3,yewu4,quyu,sfd,mdd,jsy,cfsj,kcsj,tjsj),newpath);
        }catch (IOException e){
            e.printStackTrace();
        }
        File newfile = new File(newpath);
        logger.info("高峰业务表"+filename+" 创建成功！");
        if(newfile.exists()){
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/force-download");// 设置强制下载不打开
            response.addHeader("Content-Disposition",
                    "attachment;fileName=" + new String( filename.getBytes("gb2312"), "ISO8859-1" ));// 设置文件名
            byte[] buffer = new byte[1024];
            FileInputStream fis = null;
            BufferedInputStream bis = null;
            try {
                fis = new FileInputStream(newfile);
                bis = new BufferedInputStream(fis);
                OutputStream os = response.getOutputStream();
                int i = bis.read(buffer);
                while (i != -1) {
                    os.write(buffer, 0, i);
                    i = bis.read(buffer);
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (bis != null) {
                    try {
                        bis.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
                if (fis != null) {
                    try {
                        fis.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        //文件删除
        if(newfile.delete()){
            logger.info("运单表"+filename+" 删除成功！");
        }else {
            logger.info("运单表"+filename+" 删除失败！");
        }
        return null;
    }


    private void creatPeakwaybillExcel(List<PeakwaybillView> peakwaybillViews, String newpath)throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFSheet sheet=wb.createSheet("高峰业务表");
        sheet.setColumnWidth(1,13*256);
        sheet.setColumnWidth(4,26*256);
        sheet.setColumnWidth(5,26*256);
        sheet.setColumnWidth(6,13*256);
        sheet.setColumnWidth(9,13*256);
        sheet.setColumnWidth(13,13*256);
        sheet.setColumnWidth(14,13*256);
        sheet.setColumnWidth(16,26*256);
        sheet.setColumnWidth(17,24*256);

        for(int x = 0; x<2+peakwaybillViews.size(); x++){
            // 创建新建excel Sheet的行
            HSSFRow row = sheet.createRow(x);
            // 单元格式样
            for (int y = 0; y<=17; y++) {
                row.createCell(y);
                row.getCell(y).setCellStyle(cellStyle);
            }
        }
        HSSFRow row1=sheet.getRow(0);
        row1.setHeight((short)(25*20));
        row1.getCell(0).setCellValue("运单表");
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,15));
        HSSFRow row2=sheet.getRow(1);
        row2.setHeight((short)(25*20));
        row2.getCell(0).setCellValue("业务类型");
        row2.getCell(1).setCellValue("区域");
        row2.getCell(2).setCellValue("始发地");
        row2.getCell(3).setCellValue("目的地");
        row2.getCell(4).setCellValue("靠车时间");
        row2.getCell(5).setCellValue("出发时间");
        row2.getCell(6).setCellValue("车标号");
        row2.getCell(7).setCellValue("车型");
        row2.getCell(8).setCellValue("车辆来源");
        row2.getCell(9).setCellValue("外请车车辆来源");
        row2.getCell(10).setCellValue("车牌号");
        row2.getCell(11).setCellValue("外挂车车牌号");
        row2.getCell(12).setCellValue("驾驶员");
        row2.getCell(13).setCellValue("手机号码");
        row2.getCell(14).setCellValue("备用手机号码");
        row2.getCell(15).setCellValue("油卡卡号");
        row2.getCell(16).setCellValue("异常备注");
        row2.getCell(17).setCellValue("添加时间");


        JSONArray ja = JSONArray.parseArray(JSON.toJSONString(peakwaybillViews));
        int i = 2;
        for(Object aJa : ja){
            JSONObject job = (JSONObject) aJa;
            HSSFRow row = sheet.getRow(i);
            row.setHeight((short)(20*20));
            if(job.get("ywlx")!=null){
                row.getCell(0).setCellValue(job.get("ywlx").toString());
            }else {
                row.getCell(0).setCellValue("");
            }
            if(job.get("quyu")!=null){
                row.getCell(1).setCellValue(job.get("quyu").toString());
            }else{
                row.getCell(1).setCellValue("");
            }
            if(job.get("sfd")!=null){
                row.getCell(2).setCellValue(job.get("sfd").toString());
            }else{
                row.getCell(2).setCellValue("");
            }
            if(job.get("mdd")!=null){
                row.getCell(3).setCellValue(job.get("mdd").toString());
            }else{
                row.getCell(3).setCellValue("");
            }
            if(job.get("kcsj")!=null){
                row.getCell(4).setCellValue(job.get("kcsj").toString());
            }else{
                row.getCell(4).setCellValue("");
            }
            if(job.get("cfsj")!=null){
                row.getCell(5).setCellValue(job.get("cfsj").toString());
            }else{
                row.getCell(5).setCellValue("");
            }
            if(job.get("cbh")!=null){
                row.getCell(6).setCellValue(job.get("cbh").toString());
            }else{
                row.getCell(6).setCellValue("");
            }
            if(job.get("chexing")!=null){
                row.getCell(7).setCellValue(job.get("chexing").toString());
            }else{
                row.getCell(7).setCellValue("");
            }
            if(job.get("clly")!=null){
                row.getCell(8).setCellValue(job.get("clly").toString());
            }else{
                row.getCell(8).setCellValue("");
            }
            if(job.get("wqcclly")!=null){
                row.getCell(9).setCellValue(job.get("wqcclly").toString());
            }else{
                row.getCell(9).setCellValue("");
            }
            if(job.get("cph")!=null){
                row.getCell(10).setCellValue(job.get("cph").toString());
            }else{
                row.getCell(10).setCellValue("");
            }
            if(job.get("wgccph")!=null){
                row.getCell(11).setCellValue(job.get("wgccph").toString());
            }else{
                row.getCell(11).setCellValue("");
            }
            if(job.get("jsy")!=null){
                row.getCell(12).setCellValue(job.get("jsy").toString());
            }else{
                row.getCell(12).setCellValue("");
            }
            if(job.get("sjhm")!=null){
                row.getCell(13).setCellValue(job.get("sjhm").toString());
            }else{
                row.getCell(13).setCellValue("");
            }
            if(job.get("bysjhm")!=null){
                row.getCell(14).setCellValue(job.get("bysjhm").toString());
            }else{
                row.getCell(14).setCellValue("");
            }
            if(job.get("ykkh")!=null){
                row.getCell(15).setCellValue(job.get("ykkh").toString());
            }else{
                row.getCell(15).setCellValue("");
            }
            if(job.get("ycbz")!=null){
                row.getCell(16).setCellValue(job.get("ycbz").toString());
            }else{
                row.getCell(16).setCellValue("");
            }
            if(job.get("tjsj")!=null){
                row.getCell(17).setCellValue(job.get("tjsj").toString());
            }else{
                row.getCell(17).setCellValue("");
            }
            i++;
        }
        FileOutputStream out =new FileOutputStream(newpath);
        wb.write(out);
        out.close();
    }

    //检查业务类型
    private Boolean checkYwlx(String ywlx){
        Boolean b = false;
        if(ywlx.equals("顺丰")||ywlx.equals("邮政")||ywlx.equals("京东")||ywlx.equals("其它")){
            b = true;
        }
        return b;
    }

    //检查信息长度
    private Boolean checkPeakwaybill(String peakwaybill){
        Boolean b = false;
        if(peakwaybill.matches("^.{0,20}$")){
            b = true;
        }
        return b;
    }

    //检查车标号
    private Boolean checkCbh(String cbh){
        Boolean b = false;
        if(cbh.matches("\\d{12}$")){
            b = true;
        }
        return b;
    }

    //检查车型
    private Boolean checkChexing(String chexing){
        Boolean b = false;
        if(chexing.equals("30T")||chexing.equals("20T")||chexing.equals("14T")||chexing.equals("7.1T")||chexing.equals("3T")){
            b = true;
        }
        return b;
    }

    //检查车辆来源
    private Boolean checkClly(String clly){
        Boolean b = false;
        if(clly.equals("自营车辆")||clly.equals("外请车辆")){
            b = true;
        }
        return b;
    }

    //检查车牌号格式
    private Boolean checkCph(String cph){
        Boolean b = false;
        if(cph.matches("^[冀豫云辽黑湘皖鲁苏浙赣鄂桂甘晋蒙陕吉闽贵粤青藏川宁琼渝京津沪新京军空海北沈兰济南广成使领A-Z]{1}[a-zA-Z0-9]{5}[a-zA-Z0-9挂学警港澳]{1}$")){
            b = true;
        }
        return b;
    }

    //检查驾驶员手机号格式
    private Boolean checkSjhm(String sjhm){
        Boolean b = false;
        if(sjhm.matches("\\d{11}$")){
            b = true;
        }
        return b;
    }

    //检查油卡卡号
    private Boolean checkYkkh(String ykkh){
        Boolean b = false;
        if(ykkh.matches("\\d{8}$")){
            b = true;
        }
        return b;
    }

    //检查异常备注
    private Boolean checkYcbz(String ycbz){
        Boolean b = false;
        if(ycbz.matches("^.{0,200}$")){
            b = true;
        }
        return b;
    }

    //检查汉子英文
    private Boolean checkCE(String ce){
        Boolean b = false;
        if(ce.matches("^[\\u4E00-\\u9FA5A-Za-z]+$")){
            b = true;
        }
        return b;
    }

}
